/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator.importCoordinator;
import hk.ces.oxfam.patch.OTWTeamCoordinator.JDBCUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import java.util.List;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author ivanlin
 */
public class CompanyCoordinatorDAOUtil
{
  private Connection conn;
  private PreparedStatement pst;
  private ResultSet rs;

  public CompanyCoordinatorDAOUtil()
  {

  }

  public void insertToDb(List<CompanyCoordinator> items)
  {
    this.conn = JDBCUtil.getConnection();

    int insertCount = 0;
    for (CompanyCoordinator c : items)
    {
      try
      {
        String sql = "insert into Coordinator(id, address, building, chFirstname, chLastName, coordinatorType,"
                + "correspondenceBy, correspondenceType, email, enFirstName, enMiddleName, enLastName, enOtherName,"
                + "estate, job, priPhoneCountryCode, priPhoneNo, room, secPhoneCountryCode, secPhoneNo, status, street,"
                + "title, company_id, district_id, region_id, tongue_id, defaulted, disabled, createddate, createdby) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        this.pst = this.conn.prepareStatement(sql);
        int index = 1;
        this.pst.setLong(index++, c.getId());
        this.pst.setString(index++, c.getAddress());
        this.pst.setString(index++, c.getBuilding());
        this.pst.setString(index++, c.getChFirstName());
        this.pst.setString(index++, c.getChLastName());
        this.pst.setString(index++, c.getCoordinatorType());
        this.pst.setString(index++, c.getCorrespondenceBy());
        this.pst.setString(index++, c.getCorrespondenceType());
        this.pst.setString(index++, c.getEmail());
        this.pst.setString(index++, c.getEnFirstName());
        this.pst.setString(index++, c.getEnMiddleName());
        this.pst.setString(index++, c.getEnLastName());
        this.pst.setString(index++, c.getEnOtherName());
        this.pst.setString(index++, c.getEstate());
        this.pst.setString(index++, c.getJob());
        this.pst.setString(index++, c.getPriPhoneCountryCode());
        this.pst.setString(index++, c.getPriPhoneNo());
        this.pst.setString(index++, c.getRoom());
        this.pst.setString(index++, c.getSecPhoneCountryCode());
        this.pst.setString(index++, c.getSecPhoneNo());
        this.pst.setString(index++, c.getStatus());
        this.pst.setString(index++, c.getStreet());
        this.pst.setString(index++, c.getTitle());
        this.pst.setInt(index++, c.getCompanyId());
        this.pst.setInt(index++, c.getDistrictId());
        this.pst.setInt(index++, c.getRegionId());
        this.pst.setInt(index++, c.getTongueId());
        this.pst.setBoolean(index++, c.isDefaulted());
        this.pst.setBoolean(index++, c.isDisabled());       
        this.pst.setDate(index++, new java.sql.Date(new Date().getTime()));
        this.pst.setString(index++, "DATA PATCH");

        insertCount += this.pst.executeUpdate();
      }
      catch (SQLException ex)
      {
        Logger.getLogger(CompanyCoordinatorDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
        break;
      }
      finally
      {
        JDBCUtil.releaseCursor(pst, rs);
      }
    }

    this.resetTableStartId();

    JDBCUtil.close(conn, pst, rs);

    System.out.println("Insert count : " + insertCount);
  }

  private void resetTableStartId()
  {
    int startId = 1;
    try
    {
      String sql = "select max(id) from coordinator";
      this.pst = this.conn.prepareStatement(sql);
      this.rs = this.pst.executeQuery();

      while (this.rs.next())
      {
        startId = rs.getInt(1) + 1;
      }
    }
    catch (SQLException ex)
    {

      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }


    String sql = "alter table coordinator alter id restart with " + startId;
    try
    {
      this.pst = this.conn.prepareStatement(sql);
      this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  public void addRelationshipBetweenCompanyCoordinatorAndTeamCoordinator(Map<Long, List<Long>> map)
  {
    this.conn = JDBCUtil.getConnection();
    
    int updateCount = 0;

    for (Map.Entry<Long, List<Long>> entry : map.entrySet())
    {
      long companyCoordinatorId = entry.getKey();
      List<Long> teamCoordinatorIds = entry.getValue();
      
      for (Long teamCoordinatorId : teamCoordinatorIds)
      {
        String sql = "update otwteamcoordinator set companycoordinatorid = " + companyCoordinatorId + " where id = " + teamCoordinatorId;
        try
        {
          this.pst = this.conn.prepareStatement(sql);
          updateCount += this.pst.executeUpdate();
        }
        catch (SQLException ex)
        {
          Logger.getLogger(CompanyCoordinatorDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
          JDBCUtil.releaseCursor(pst, rs);
        }
      }
    }

    JDBCUtil.close(conn, pst, rs);

    System.out.println("update count : " + updateCount);
  }

  public long getMaxId() throws SQLException
  {
    String sql = "select max(id) from coordinator";
    this.conn = JDBCUtil.getConnection();
    this.pst = this.conn.prepareStatement(sql);
    this.rs = this.pst.executeQuery();

    long maxId = 0;
    while (rs.next())
    {
      maxId = rs.getLong(1);
    }
    JDBCUtil.close(conn, pst, rs);
    return maxId;
  }
}
